#!/usr/bin/python
################################################################################
# Licensed to the OpenAirInterface (OAI) Software Alliance under one or more
# contributor license agreements.  See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The OpenAirInterface Software Alliance licenses this file to You under
# the Apache License, Version 2.0  (the "License"); you may not use this file
# except in compliance with the License.
# You may obtain a copy of the License at
#
#      http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#-------------------------------------------------------------------------------
# For more information about the OpenAirInterface (OAI) Software Alliance:
#      contact@openairinterface.org
################################################################################
# file populate_hss_mysql_db
# brief
# main author Anta Huang
# company Eurecom
# email: anta.huang@eurecom.fr
# other author Lionel GAUTHIER for python conversion

import MySQLdb
import argparse
 

def connect_to_database(mysql_host, mysql_root_password):
    return MySQLdb.connect(host=mysql_host,user="root",passwd=mysql_root_password)


def create_database(cursor, database, mysql_host, user, password):
    sql1 = "DROP DATABASE IF EXISTS %s;" % (database)
    sql2_a = "GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'linux' WITH GRANT OPTION;" 
    sql2_b = "GRANT ALL PRIVILEGES ON *.* TO '%s'@'%s' IDENTIFIED BY '%s' WITH GRANT OPTION;" % (user, mysql_host, password)
    sql3 = "FLUSH PRIVILEGES;"
    sql4 = "CREATE DATABASE %s;" % (database)
    sql5 = "USE %s;" % (database)

    cursor.execute(sql1)
    cursor.execute(sql2_a)
    cursor.execute(sql2_b)
    cursor.execute(sql3)
    cursor.execute(sql4)
    cursor.execute(sql5)


def create_table_mme_identity(cursor, database, mysql_host, user, password):

    cursor.execute(sql1)
    cursor.execute(sql2)
    cursor.execute(sql3)
    cursor.execute(sql4)
    cursor.execute(sql5)



def main():

    MMEIDENTITY='''CREATE TABLE `mmeidentity` ( \
                `idmmeidentity` int(11) NOT NULL AUTO_INCREMENT, \
                `mmehost` varchar(255) DEFAULT NULL,\
                `mmerealm` varchar(200) DEFAULT NULL,\
                `UE-Reachability` tinyint(1) NOT NULL COMMENT 'Indicates whether the MME supports UE Reachability Notifcation',\
                PRIMARY KEY (`idmmeidentity`)\
                ) ENGINE=MyISAM AUTO_INCREMENT=46 DEFAULT CHARSET=latin1;'''

    PDN="CREATE TABLE `pdn` (\
              `id` int(11) NOT NULL AUTO_INCREMENT,\
              `apn` varchar(60) NOT NULL,\
              `pdn_type` enum('IPv4','IPv6','IPv4v6','IPv4_or_IPv6') NOT NULL DEFAULT 'IPv4',\
              `pdn_ipv4` varchar(15) DEFAULT '0.0.0.0',\
              `pdn_ipv6` varchar(45) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT '0:0:0:0:0:0:0:0',\
              `aggregate_ambr_ul` int(10) unsigned DEFAULT '50000000',\
              `aggregate_ambr_dl` int(10) unsigned DEFAULT '100000000',\
              `pgw_id` int(11) NOT NULL,\
              `users_imsi` varchar(15) NOT NULL,\
              `qci` tinyint(3) unsigned NOT NULL DEFAULT '9',\
              `priority_level` tinyint(3) unsigned NOT NULL DEFAULT '15',\
              `pre_emp_cap` enum('ENABLED','DISABLED') DEFAULT 'DISABLED',\
              `pre_emp_vul` enum('ENABLED','DISABLED') DEFAULT 'DISABLED',\
              `LIPA-Permissions` enum('LIPA-prohibited','LIPA-only','LIPA-conditional') NOT NULL DEFAULT 'LIPA-only',\
              PRIMARY KEY (`id`,`pgw_id`,`users_imsi`),\
              KEY `fk_pdn_pgw1_idx` (`pgw_id`),\
              KEY `fk_pdn_users1_idx` (`users_imsi`)\
            ) ENGINE=MyISAM AUTO_INCREMENT=60 DEFAULT CHARSET=latin1;"
    

    USERS="CREATE TABLE `users` (\
          `imsi` varchar(15) NOT NULL COMMENT 'IMSI is the main reference key.',\
          `msisdn` varchar(46) DEFAULT NULL COMMENT 'The basic MSISDN of the UE (Presence of MSISDN is optional).',\
          `imei` varchar(15) DEFAULT NULL COMMENT 'International Mobile Equipment Identity',\
          `imei_sv` varchar(2) DEFAULT NULL COMMENT 'International Mobile Equipment Identity Software Version Number',\
          `ms_ps_status` enum('PURGED','NOT_PURGED') DEFAULT 'PURGED' COMMENT 'Indicates that ESM and EMM status are purged from MME',\
          `rau_tau_timer` int(10) unsigned DEFAULT '120',\
          `ue_ambr_ul` bigint(20) unsigned DEFAULT '50000000' COMMENT 'The Maximum Aggregated uplink MBRs to be shared across all Non-GBR bearers according to the subscription of the user.',\
          `ue_ambr_dl` bigint(20) unsigned DEFAULT '100000000' COMMENT 'The Maximum Aggregated downlink MBRs to be shared across all Non-GBR bearers according to the subscription of the user.',\
          `access_restriction` int(10) unsigned DEFAULT '47' COMMENT 'Indicates the access restriction subscription information. 3GPP TS.29272 #7.3.31',\
          `mme_cap` int(10) unsigned zerofill DEFAULT NULL COMMENT 'Indicates the capabilities of the MME with respect to core functionality e.g. regional access restrictions.',\
          `mmeidentity_idmmeidentity` int(11) NOT NULL DEFAULT '0',\
          `key` varbinary(16) NOT NULL DEFAULT '0' COMMENT 'UE security key',\
          `RFSP-Index` smallint(5) unsigned NOT NULL DEFAULT '1' COMMENT 'An index to specific RRM configuration in the E-UTRAN. Possible values from 1 to 256',\
          `urrp_mme` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'UE Reachability Request Parameter indicating that UE activity notification from MME has been requested by the HSS.',\
          `sqn` bigint(20) unsigned zerofill NOT NULL DEFAULT '32',\
          `rand` varbinary(16) NOT NULL,\
          `OPc` varbinary(16) DEFAULT NULL COMMENT 'Can be computed by HSS',\
          PRIMARY KEY (`imsi`,`mmeidentity_idmmeidentity`),\
          KEY `fk_users_mmeidentity_idx1` (`mmeidentity_idmmeidentity`)\
        ) ENGINE=MyISAM DEFAULT CHARSET=latin1;"


    parser = argparse.ArgumentParser()
    parser.add_argument('-a', '--apn',        action="append",   default=['oai.ipv4'], help="APN(s) allowed for all IMSI of this HSS db")
    parser.add_argument('-d', '--database',   default='oai',                 help="database name")
    parser.add_argument('-H', '--mysql_host', default='127.0.0.1',           help="mysql server binded ip address")
    parser.add_argument('-k', '--key',        default='8baf473f2f8fd09487cccbd7097c6862', help="LTE K (common for all IMSI)=8baf473f2f8fd09487cccbd7097c6862 for OP=11111111111111111111111111111111\n\
                                                                                                LTE K (common for all IMSI)=fec86ba6eb707ed08905757b1bb44b8f for OP=1006020f0a478bf6b699f15c062e42b3")
    parser.add_argument('-M', '--msisdn-first', default='+33663000021',      help="MSISDN of users (TODO: distincts MSISDN)")
    parser.add_argument('-m', '--mme_identity', action="append", required=True,   help="Granted MME access to HSS")
    parser.add_argument('--mysql_root_password', default='linux',            help="Mysql password of the root user")
    parser.add_argument('-u', '--user',       default='hssadmin',            help="Create admin user name of specified database")
    parser.add_argument('-p', '--password',   default='admin',               help="Create admin user password of specified database")
    parser.add_argument('-P','--plmn',        default='20895',               help="PLMN")
    parser.add_argument('--sqn',              default='32',                  help="SQN")
    parser.add_argument('-S','--msin-first',  default='0000000001',          help="First MSIN for creating users (IMSI)")
    parser.add_argument('-s','--msin-last',   default='0000001000',          help="Last MSIN for creating users (IMSI)")
    parser.add_argument('-r', '--realm',      default='openair4G.eur',       help="Realm of the CN")
    args = parser.parse_args()

    con = connect_to_database(args.mysql_host, args.mysql_root_password)
    cur = con.cursor()
    create_database(cur, args.database, args.mysql_host, args.user, args.password)

    # Create tables
    cur.execute(MMEIDENTITY)
    cur.execute(PDN)
    cur.execute(USERS)
    
    # Populate tables
    primary_key = 0
    ue_reachability = 1
    for mme in args.mme_identity:
        primary_key += 1
        sql_mme_identity = "INSERT INTO `mmeidentity` VALUES ('%d','%s','%s','%d')" % (primary_key, mme, args.realm, ue_reachability)
        cur.execute(sql_mme_identity)
    
    # Table PDN and USERS
    primary_key = 0
    msin_first_int = int(args.msin_first)
    msin_last_int  = int(args.msin_last)
    msin_length = max (len(args.msin_first), len(args.msin_last))
    imei = int(35609204079301)
    qci = int(9)
    for msin in range (msin_first_int, msin_last_int):
        primary_key += 1
        msin_str = str(msin)
        msin_str = msin_str.zfill(msin_length)
        imsi = args.plmn + msin_str
        for apn in args.apn:
            sql_pdn="INSERT INTO `pdn` VALUES (NULL,'%s','IPv4','0.0.0.0','0:0:0:0:0:0:0:0',50000000,100000000,3,'%s',%d,15,'DISABLED','ENABLED','LIPA-only')" % (apn, imsi, qci)
            cur.execute(sql_pdn)

        sql_users="INSERT INTO `users` VALUES ('%s', '%s', '%d', '88', 'PURGED', '120', '50000000', '100000000', '47', NULL, '0', X'%s', '1', '0', '%s', '', 1)" % (imsi, args.msisdn_first, imei, args.key, args.sqn)
        imei += 1
        cur.execute(sql_users)
    
    
    con.commit()
    cur.close()
    con.close()
    
if __name__ == "__main__":
    main()






